import tkinter as tk
from tkinter import messagebox
import pymssql
import datetime

def open(s):
    # 创建主窗口
    root = tk.Tk()
    root.title("园林植物监测")
    root.geometry("500x300")
    def add_data():
      id=id_entry.get()
      time = time_entry.get()
      person = person_entry.get()
      location = location_entry.get()
      object = object_entry.get()
      device = device_entry.get()
      cjry = cjry_entry.get()
      cjsj = cjsj_entry.get()
      gxsj = gxsj_entry.get()
    # 将数据插入到数据库中
      conn = pymssql.connect(
        host='localhost',
        server='DESKTOP-O4JOJ5U\SQLEXPRESS',
        user='SQLserver',
        password='123456',
        database='植物管理系统   ',
        as_dict=True
      )
      c = conn.cursor()
      c.execute(f"INSERT INTO 监测 (监测编号,监测时间,监测人员,监测对象,监测地点,监测设备,创建人员,创建时间,更新时间) VALUES ('{id}', '{time}','{person}','{object}','{location}','{device}','{cjry}','{cjsj}','{gxsj}')")
      conn.commit()
      conn.close()
      # 清空输入框
      id_entry.delete(0, tk.END)
      time_entry.delete(0, tk.END)
      person_entry.delete(0, tk.END)
      location_entry.delete(0, tk.END)
      object_entry.delete(0, tk.END)
      device_entry.delete(0, tk.END)
      cjry_entry.delete(0, tk.END)
      cjsj_entry.delete(0, tk.END)
      gxsj_entry.delete(0, tk.END)
#修改
    def update_data():
        # 获取输入框中的数据
        id = id_entry.get()
        #time = time_entry.get()
        time_str = time_entry.get()
        time_obj = datetime.datetime.strptime(time_str, "%Y-%m-%d")
        time_str = time_obj.strftime("%Y-%m-%d")

        person = person_entry.get()
        location = location_entry.get()
        object = object_entry.get()
        device = device_entry.get()
        cjry = cjry_entry.get()
        cjsj = cjsj_entry.get()
        cjsj_obj = datetime.datetime.strptime(cjsj, "%Y-%m-%d")
        cjsj = cjsj_obj.strftime("%Y-%m-%d")

        gxsj = gxsj_entry.get()
        gxsj_obj = datetime.datetime.strptime(gxsj, "%Y-%m-%d")
        gxsj = gxsj_obj.strftime("%Y-%m-%d")

        # 更新数据库中的数据
        conn = pymssql.connect(
            host='localhost',
            server='DESKTOP-O4JOJ5U\SQLEXPRESS',
            user='SQLserver',
            password='123456',
            database='植物管理系统',
            as_dict=True
        )
        c = conn.cursor()
        c.execute(
            f"UPDATE 监测 SET  监测时间='{time_str}', 监测人员='{person}', 监测地点='{location}', 监测对象='{object}', 监测设备='{device}' ,创建人员='{cjry}',创建时间='{cjsj}',更新时间='{gxsj}' WHERE 监测编号=%s",(id))
        conn.commit()
        conn.close()
        '''c.execute("UPDATE 监测 SET  监测时间=%s, 监测人员=%s, 监测地点=%s, 监测对象=%s, 监测设备=%s ,创建人员=%s,创建时间=%s,更新时间=%s WHERE 监测编号=%s",
                   ('{time}', '{person}', {location}, {object}, {device}, {id},{cjry},{cjsj},{gxsj}))'''
        # 清空输入框
        id_entry.delete(0, tk.END)
        time_entry.delete(0, tk.END)
        person_entry.delete(0, tk.END)
        location_entry.delete(0, tk.END)
        object_entry.delete(0, tk.END)
        device_entry.delete(0, tk.END)
        cjry_entry.delete(0, tk.END)
        cjsj_entry.delete(0, tk.END)
        gxsj_entry.delete(0, tk.END)
    # 删除
    def delete_data():
        # 获取输入框中的ID
        id = id_entry.get()

        # 从数据库中删除数据
        conn = pymssql.connect(
            host='localhost',
            server='DESKTOP-O4JOJ5U\SQLEXPRESS',
            user='SQLserver',
            password='123456',
            database='植物管理系统',
            as_dict=True
        )
        c = conn.cursor()
        c.execute("DELETE FROM 监测 WHERE 监测编号=%s", (id,))
        conn.commit()
        conn.close()

        # 清空输入框
        id_entry.delete(0, tk.END)

    # 查询
    def query_data():
        # 获取输入框中的ID
        id = id_entry.get()

        # 从数据库中查询数据
        conn = pymssql.connect(
            host='localhost',
            server='DESKTOP-O4JOJ5U\SQLEXPRESS',
            user='SQLserver',
            password='123456',
            database='植物管理系统',
            as_dict=True
        )
        c = conn.cursor()
        c.execute("SELECT * FROM 监测 WHERE 监测编号=%s", (id,))
        data = c.fetchone()

        # 显示查询结果
        if data:
            time_entry.delete(0, tk.END)
            person_entry.delete(0, tk.END)
            location_entry.delete(0, tk.END)
            object_entry.delete(0, tk.END)
            device_entry.delete(0, tk.END)
            cjry_entry.delete(0, tk.END)
            cjsj_entry.delete(0, tk.END)
            gxsj_entry.delete(0, tk.END)
            time_entry.insert(0, data['监测时间'])
            person_entry.insert(0, data['监测人员'])
            object_entry.insert(0, data['监测对象'])
            location_entry.insert(0, data['监测地点'])
            device_entry.insert(0, data['监测设备'])
            cjry_entry.insert(0, data['创建人员'])
            cjsj_entry.insert(0, data['创建时间'])
            gxsj_entry.insert(0, data['更新时间'])
        else:
            messagebox.showerror("错误", "未找到对应的数据")

        conn.close()

    # 创建输入框和标签
    id_label = tk.Label(root, text="监测编号")
    id_label.grid(row=0, column=0)
    id_entry = tk.Entry(root)
    id_entry.grid(row=0, column=1)

    time_label = tk.Label(root, text="监测时间")
    time_label.grid(row=1, column=0)
    time_entry = tk.Entry(root)
    time_entry.grid(row=1, column=1)

    person_label = tk.Label(root, text="监测人员")
    person_label.grid(row=2, column=0)
    person_entry = tk.Entry(root)
    person_entry.grid(row=2, column=1)

    location_label = tk.Label(root, text="监测对象")
    location_label.grid(row=3, column=0)
    location_entry = tk.Entry(root)
    location_entry.grid(row=3, column=1)

    object_label = tk.Label(root, text="监测地点")
    object_label.grid(row=4, column=0)
    object_entry = tk.Entry(root)
    object_entry.grid(row=4, column=1)

    device_label = tk.Label(root, text="监测设备")
    device_label.grid(row=5, column=0)
    device_entry = tk.Entry(root)
    device_entry.grid(row=5, column=1)

    cjry_label = tk.Label(root, text="创建人员")
    cjry_label.grid(row=6, column=0)
    cjry_entry = tk.Entry(root)
    cjry_entry.grid(row=6, column=1)

    cjsj_label = tk.Label(root, text="创建时间")
    cjsj_label.grid(row=7, column=0)
    cjsj_entry = tk.Entry(root)
    cjsj_entry.grid(row=7, column=1)

    gxsj_label = tk.Label(root, text="更新时间")
    gxsj_label.grid(row=8, column=0)
    gxsj_entry = tk.Entry(root)
    gxsj_entry.grid(row=8, column=1)

    # 创建按钮并绑定事件处理函数
    add_button = tk.Button(root, text="添加", command=add_data)
    add_button.grid(row=9, column=0)
    update_button = tk.Button(root, text="修改", command=update_data)
    update_button.grid(row=9, column=1)
    delete_button = tk.Button(root, text="删除", command=delete_data)
    delete_button.grid(row=9, column=2)
    query_button = tk.Button(root, text="查询", command=query_data)
    query_button.grid(row=9, column=3)
    root.mainloop()

